USE SchoolManager;
IF OBJECT_ID (N'dbo.StudentInCourse', N'U') IS NOT NULL
DROP TABLE dbo.StudentInCourse;
CREATE TABLE [dbo].[StudentInCourse] (
    [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
	[StudentID] [uniqueidentifier] NOT NULL,
	[CourseID] [int] NOT NULL
);
ALTER TABLE [dbo].[StudentInCourse]
    ADD CONSTRAINT [PK_StudentInCourse] PRIMARY KEY CLUSTERED ([EnrollmentID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
    
ALTER TABLE [dbo].[StudentInCourse]
    ADD CONSTRAINT [FK_StudentInCourse_Course] FOREIGN KEY ([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE [dbo].[StudentInCourse]
    ADD CONSTRAINT [FK_StudentInCourse_Student] FOREIGN KEY ([StudentID]) REFERENCES [dbo].[aspnet_Users] ([UserID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE [dbo].[Student]
    ADD CONSTRAINT [FK_Student_aspnetUsers] FOREIGN KEY ([UniqueID]) REFERENCES [dbo].[aspnet_Users] ([UserId]) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE [dbo].[Department]
    ADD CONSTRAINT [FK_Department_FacultyMember] FOREIGN KEY ([DepartmentHead]) REFERENCES [dbo].[FacultyMember] ([UniqueID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE [dbo].[Course]
    ADD CONSTRAINT [FK_Course_FacultyMember] FOREIGN KEY ([Teacher]) REFERENCES [dbo].[FacultyMember] ([UniqueID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE [dbo].[Course]
    ADD CONSTRAINT [FK_Course_Department] FOREIGN KEY ([Department]) REFERENCES [dbo].[Department] ([DepartmentID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
    






